#!/usr/local/bin/php
<?php
	error_reporting(E_ALL);
	ini_set('display_errors', 1);
	require_once('sync_config.php');
	error_log(date('d/m/Y H:i:s') . " | -------------- \n");
	error_log(date('d/m/Y H:i:s') . " | Start cron job \n", 3, $log_file);

	// connect to source database
	$src_db_con = mysql_connect($src_db_host, $src_db_username, $src_db_password);
	if(!$src_db_con || !mysql_select_db($src_db_name, $src_db_con)){
		error_log(date('d/m/Y H:i:s') . " | Could not connect to source database, or the database name is incorrect \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, 'r');
		fputcsv($fp, array(date('m/d/Y H:i:s')));
		fclose($fp);
		error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
		exit;
	}

	// connect to destination database
	$dst_db_con = mysql_connect($dst_db_host, $dst_db_username, $dst_db_password);
	if(!$dst_db_con || !mysql_select_db($dst_db_name, $dst_db_con)){
		error_log(date('d/m/Y H:i:s') . " | Can not connect to destination database, or the database name is incorrect \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, 'a+');
		fputcsv($fp, array(date('m/d/Y H:i:s')));
		fclose($fp);
		error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
		exit;
	}

	// synchronize for the previous hour
	error_log(date('d/m/Y H:i:s') . " | Synchronize data for the previous hour \n", 3, $log_file);
	if(sync(date('m/d/Y H:i:s'), $src_db_con, $dst_db_con, $fail_datetime_file, $log_file)){
		// synchronize for the previous failed hour
		error_log(date('d/m/Y H:i:s') . " | Synchronize data for the previous failed hours \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, "r");
		if(!$fp){
			error_log(date('d/m/Y H:i:s') . " | Can not open the file $fail_datetime_file which stores previous fialed hours \n", 3, $log_file);
		}else{
			$fail_datetime_array = fgetcsv($fp, null, ',');
			fclose($fp);
			while($fail_datetime_array){
				$fail_datetime = array_pop($fail_datetime_array);
				if($fail_datetime != NULL){
					if(!sync($fail_datetime, $src_db_con, $dst_db_con, $fail_datetime_file, $log_file)){
						$fp = fopen($fail_datetime_file, 'w+');
						fputcsv($fp, $fail_datetime_array, ',' );
						fclose($fp);
						error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
						break;
					}
				}
			}
			if(count($fail_datetime_array) == 0){
				$fp = fopen($fail_datetime_file, 'w+');
				fclose($fp);
			}
		}
	}
	error_log(date('d/m/Y H:i:s') . " | Exit cron job \n", 3, $log_file);

	// close the databases
	mysql_close($src_db_con);
	mysql_close($dst_db_con);

	function sync($datetime, $src_db_con, $dst_db_con, $fail_datetime_file, $log_file){
		global $fee;
		$timestamp = strtotime($datetime);
		// get the select date time
		$prevTimestamp = $timestamp - 60*60;
		$currDay = date('d', $timestamp);
		$currHour = date('H', $timestamp);
		$currMonth = date('m', $timestamp);
		$currYear = date('Y', $timestamp);

		$prevDay = date('d', $prevTimestamp );
		$prevMonth = date('m', $prevTimestamp );
		$prevHour = date('H', $prevTimestamp);
		$prevYear = date('Y', $prevTimestamp);
		$prevDatetime = $prevMonth . "/" . $prevDay . "/" . $prevYear . " " . $prevHour . ":00:00";
		$currDatetime = $currMonth . "/" . $currDay . "/" . $currYear . " " . $currHour . ":00:00";

		error_log(date('d/m/Y H:i:s') . " | Synchronize for date: $prevDatetime \n", 3, $log_file);

		$mo_table = 'mo' . $prevYear . $prevMonth . $prevDay;
		$mt_table = 'mt' . $prevYear . $prevMonth . $prevDay;
		$cdr_table = 'cdrreport' . $prevYear . $prevMonth . $prevDay;
		$billing_table = 'vmg_billing_count' . $prevYear . $prevMonth;
		$dest_table = 'vmg_billing_count' . $prevYear . $prevMonth;
		$service_table = 'vmg_service';
		$keywords = "";

		//select all xzone keywords from vmg_service
		$select = "select group_concat(distinct keywords) as keywords from " . $service_table;
		$result = mysql_query($select, $dst_db_con);

		if(!$result){
			error_log(date('d/m/Y H:i:s') . " | Could not successfully run query from the destination database | $select | "
				. mysql_error($dst_db_con) . "\n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			return false;  // return false to abort the failed previous hour synchronization
		}

		if (mysql_num_rows($result) == 0) {
		    error_log(date('d/m/Y H:i:s') . " | There is no keywords from the destination database \n", 3, $log_file);
		    error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			return false;  // return false to abort the failed previous hour synchronization
		}

		if ($row = mysql_fetch_assoc($result)) {
			$keywords = $row["keywords"];
		}
		mysql_free_result($result);

		if (empty($keywords)) {
			error_log(date('d/m/Y H:i:s') . " | There is no keywords from the destination database \n", 3, $log_file);
		    error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			return false;  // return false to abort the failed previous hour synchronization
		}

		$keywords = "'" . $keywords . "'";
		$keywords = str_replace(",", "','", $keywords);

		// Create table if not exist
		$create_table = "CREATE TABLE IF NOT EXISTS `" . $dest_table . "` (
			  `id` int(11) NOT NULL auto_increment,
			  `date` datetime NOT NULL,
			  `msisdn` varchar(20) NOT NULL,
			  `operator` varchar(64) NOT NULL,
			  `short_code` varchar(20) NOT NULL,
			  `keyword` varchar(20) NOT NULL,
			  `mo_count` int(11) NOT NULL,
			  `mt_count` int(11) NOT NULL,
			  `cdr_count` int(11) NOT NULL default '0',
			  `error_mo` int(11) NOT NULL default '0',
			  `error_mt` int(11) NOT NULL default '0',
			  `money` int(11) NOT NULL default '0',
			  `repeated_cdr` int(11) NOT NULL default '0',
			  `spam` int(11) NOT NULL default '0',
			  PRIMARY KEY  (`id`),
			  UNIQUE KEY `uq_" . $dest_table . "` (`date`,`msisdn`,`short_code`,`operator`)
		) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;";

		if(!mysql_query($create_table, $dst_db_con)){
			error_log(date('d/m/Y H:i:s') . " | Can not create the source database table \n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, array(date('m/d/Y H:i:s')));
			fclose($fp);
			return false;
		}else{
			error_log(date('d/m/Y H:i:s') . " | Destination database table created \n", 3, $log_file);
		}

		$select_mo = "select str_to_date(date_format(date,'%m/%d/%Y %H:00:00'), '%m/%d/%Y %T')as dt, sender as msisdn, upper(substring_index(operator,'_', 1)) as operator, phone as short_code, upper(substring_index(service,':',-1)) as keyword, count(*) as mocount
				from $mo_table
				where hour(acked)= " . $prevHour . "	
				and upper(substring_index(service,':',-1)) in (" . $keywords . ")
				and operator <> 'NA' and operator is not null and trim(operator) <> ''
				and status = 0
				group by dt, msisdn, operator, short_code, keyword;	";
		
		$select_mt = "select str_to_date(date_format(date,'%m/%d/%Y %H:00:00'), '%m/%d/%Y %T')as dt, phone as msisdn, upper(substring_index(substring_index(tag,'_', 2),'_',-1)) as operator, sender as short_code, upper(substring_index(service,':',-1)) as keyword, count(*) as mtcount
			from $mt_table
			where hour(acked)= " . $prevHour . "			
			and upper(substring_index(service,':',-1)) in (" . $keywords . ")
			and tag <> 'NA' and tag is not null and trim(tag) <> ''
			and status = 0
			and sender <> ''
			group by dt, msisdn, operator, short_code, keyword";
		
		$select_cdr = "select str_to_date(concat(substring(delivertime,1,10),':00:00'), '%Y%m%d%T')as dt, concat('+',userid) as msisdn, upper(substring_index(operator,'_', 1)) as operator, serviceid as short_code, upper(keyword) as keyword, count(*) as cdrcount
			from $cdr_table
			where hour(delivertime)= " . $prevHour . "			
			and upper(keyword) in (" . $keywords . ")
			and operator <> 'NA' and operator is not null and trim(operator) <> ''
			and typemessage = 1
			group by dt, msisdn, operator, short_code, keyword";
	
		// update MO
		$result = mysql_query($select_mo, $src_db_con);				
		if(!$result){
			error_log(date('d/m/Y H:i:s') . " | Could not successfully run query from the source database | $select_mo | "
				. mysql_error($src_db_con) . "\n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, date('m/d/Y H:i:s') );
			fclose($fp);
			return false;
		}

		if (mysql_num_rows($result) == 0) {
		    error_log(date('d/m/Y H:i:s') . " | There is no MO data from the source database \n", 3, $log_file);		    
		}else{
			while ($row = mysql_fetch_assoc($result)) {
				$insert_mo = 'INSERT INTO ' . $billing_table . "(date, msisdn, operator, short_code, keyword, mo_count) VALUE";
				$operator = $row['operator'];
				$pos = strpos($operator, "_");
				if ($pos > 0) {
					$operator = substr($operator, 0, $pos - 1);
				}
				$operator = strtoupper($operator);
				$insert_mo .= "('" . $row["dt"] . "','" . $row['msisdn'] . "','" . $operator . "','" . $row["short_code"] . "','" . $row["keyword"] . "'," . $row["mocount"] . ")";
				$rt = mysql_query($insert_mo, $dst_db_con);
				if(!$rt){
					error_log(date('d/m/Y H:i:s') . " | Could not insert the MO data into the destination database | " . $insert_mo . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
				}else{
					error_log(date('d/m/Y H:i:s') . " | Inserted the MO data into the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
				}
			}			
			mysql_free_result($result);
		}
		
		$result = mysql_query($select_mt, $src_db_con);
		if(!$result){
			error_log(date('d/m/Y H:i:s') . " | Could not successfully run query from the source database | $select_mt | "
				. mysql_error($src_db_con) . "\n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, date('m/d/Y H:i:s') );
			fclose($fp);
			return false;
		}

		if (mysql_num_rows($result) == 0) {
		    error_log(date('d/m/Y H:i:s') . " | There is no MT data from the source database \n", 3, $log_file);		    
		}else{
			while ($row = mysql_fetch_assoc($result)) {
				$operator = $row['operator'];
				$pos = strpos($operator, "_");
				if ($pos > 0) {
					$operator = substr($operator, 0, $pos - 1);
				}
				$operator = strtoupper($operator);
				$select = 'SELECT id FROM ' . $billing_table 
							. " WHERE date = '" . $row["dt"] . "'"
							. " AND msisdn = '" . $row["msisdn"] . "'"
				//			. " AND operator = '" . $operator . "'"
							. " AND short_code = '". $row["short_code"] . "'" 
							. " AND keyword = '". $row["keyword"] . "'";							
				$s_result = mysql_query($select, $dst_db_con);
				if(mysql_num_rows($s_result) == 0){							
					$insert_mt = 'INSERT INTO ' . $billing_table . "(date, msisdn, operator, short_code, keyword, mt_count) VALUE";			
					$insert_mt .= "('" . $row["dt"] . "','" . $row['msisdn'] . "','" . $operator . "','" . $row["short_code"] . "','" . $row["keyword"] . "'," .  $row["mtcount"] . ")";
					$rt = mysql_query($insert_mt, $dst_db_con);
					if(!$rt){
						error_log(date('d/m/Y H:i:s') . " | Could not insert the MT data into the destination database | " . $insert_mt . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}else{
						error_log(date('d/m/Y H:i:s') . " | Inserted the MT data into the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}
				}else{
					$update_mt = 'UPDATE ' . $billing_table 
							. " SET mt_count = " . $row["mtcount"]
							. " WHERE date = '" . $row["dt"] . "'" 
							. " AND msisdn = '" . $row["msisdn"] . "'"
					//		. " AND operator = '" . $operator . "'" 
							. " AND short_code = '" . $row["short_code"] . "'"
							. " AND keyword = '" . $row["keyword"] . "'";
					$rt = mysql_query($update_mt, $dst_db_con);
					if(!$rt){
						error_log(date('d/m/Y H:i:s') . " | Could not update the MT data in the destination database | " . $update_mt . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}else{
						error_log(date('d/m/Y H:i:s') . " | Updated the MT data to the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}
					mysql_free_result($rt);							 
				}
				mysql_free_result($s_result);
			}			
			mysql_free_result($result);
		}
		
		$result = mysql_query($select_cdr, $src_db_con);	// update CDR
		if(!$result){
			error_log(date('d/m/Y H:i:s') . " | Could not successfully run query from the source database | $select_cdr | "
				. mysql_error($src_db_con) . "\n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, date('m/d/Y H:i:s') );
			fclose($fp);
			return false;
		}

		if (mysql_num_rows($result) == 0) {
		    error_log(date('d/m/Y H:i:s') . " | There is no CDR data from the source database \n", 3, $log_file);		    
		}else{
			while ($row = mysql_fetch_assoc($result)) {
				$operator = $row['operator'];
				$pos = strpos($operator, "_");
				if ($pos > 0) {
					$operator = substr($operator, 0, $pos - 1);
				}
				$operator = strtoupper($operator);
				$select = 'SELECT id FROM ' . $billing_table 
							. " WHERE date = '" . $row["dt"] . "'"
							. " AND msisdn = '" . $row["msisdn"] . "'"
				//			. " AND operator = '" . $operator . "'"
							. " AND short_code = '". $row["short_code"] . "'" 
							. " AND keyword = '". $row["keyword"] . "'";							
				$s_result = mysql_query($select, $dst_db_con);
				if(mysql_num_rows($s_result) == 0){							
					$insert_cdr = 'INSERT INTO ' . $billing_table . "(date, msisdn, operator, short_code, keyword, cdr_count, money) VALUE";			
					$insert_cdr .= "('" . $row["dt"] . "','" . $row['msisdn'] . "','" . $operator . "','" . $row["short_code"] . "','" . $row["keyword"] . "'," . $row["cdrcount"] . "," . ($fee[$operator][$row["short_code"]] * $row["cdrcount"]) . ")";
					$rt = mysql_query($insert_cdr, $dst_db_con);
					if(!$rt){
						error_log(date('d/m/Y H:i:s') . " | Could not insert the CDR data into the destination database | " . $insert_cdr . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}else{
						error_log(date('d/m/Y H:i:s') . " | Inserted the CDR data into the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}
				}else{
					$update_cdr = 'UPDATE ' . $billing_table 
							. " SET cdr_count = " . $row["cdrcount"] . ","
							. " money = " . ($fee[$operator][$row["short_code"]] * $row["cdrcount"])
							. " WHERE date = '" . $row["dt"] . "'" 
							. " AND msisdn = '" . $row["msisdn"] . "'"
					//		. " AND operator = '" . $operator . "'" 
							. " AND short_code = '" . $row["short_code"] . "'"
							. " AND keyword = '" . $row["keyword"] . "'";
					$rt = mysql_query($update_cdr, $dst_db_con);
					if(!$rt){
						error_log(date('d/m/Y H:i:s') . " | Could not update the CDR data in the destination database | " . $update_cdr . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}else{
						error_log(date('d/m/Y H:i:s') . " | Updated the CDR data into the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
					}
					mysql_free_result($rt);							 
				}
				mysql_free_result($s_result);
			}			
			mysql_free_result($result);
		}		
		error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
		return true;
	}

?>
